import pymysql
import requests
from lxml import etree
from bs4 import BeautifulSoup
import csv
from urllib.parse import urljoin
head={'user-agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/133.0.0.0 Safari/537.36'}
def get_html(url):
    try:
        r=requests.get(url=url,headers=head)
        r.encoding=r.apparent_encoding
        r.raise_for_status()
        return r.text
    except Exception as f:
        print(f)
def parser(html):
    doc=BeautifulSoup(html,'lxml')
    out_list=[]
    for i in doc.select('#tab-book > div.col-md-8.col-sm-8.main > div.g-main > div > ul > li'):
        a=i.select('div.book-info > h4 > a')[0].text.strip()
        b=i.select('div.book-info > div > span')[0].text.strip()
        c=i.select('div.book-info > span > span')[0].text.strip()
        href=i.select('div.book-img > a')[0].attrs['href'].strip()
        url_all='http://www.ryjiaoyu.com'
        new_url=urljoin(url_all,href)
        i_date=[a,b,c,new_url]
        out_list.append(i_date)
    return out_list
def save_mysql(sql,val,**dbinfo):
    try:
        connect=pymysql.connect(**dbinfo)
        cursor=connect.cursor()
        cursor.executemany(sql,val)
        connect.commit()
    except Exception as o:
        connect.rollback()
        print(o)
    finally:
        cursor.close()
        connect.close()
if __name__ == '__main__':
    url='http://www.ryjiaoyu.com/book'
    html=get_html(url)
    out_list=parser(html)
    parms={
        'host':'127.0.0.1',
        'user':'root',
        'password':'root',
        'db':'123',
        'charset':'utf8',
        'cursorclass':pymysql.cursors.DictCursor
    }
    sql='insert into zhaohuachao(a,b,c,href)''values(%s,%s,%s,%s)'
    save_mysql(sql,out_list,**parms)